
The relational schema in SQL DDL code
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS PlacesOrder;
DROP TABLE IF EXISTS Flowers;
DROP TABLE IF EXISTS Admin;
DROP TABLE IF EXISTS Contain;

CREATE TABLE Customer(
 cid CHAR(5)        DEFAULT 'C0000',
 name VARCHAR(50)   NOT NULL,
 phoneNo CHAR(8)    NOT NULL,
 email VARCHAR(128) NOT NULL UNIQUE,
 password CHAR(6)   NOT NULL,
 PRIMARY KEY(cid),
);


CREATE TABLE PlacesOrder(
 oid              INTEGER,
 cid              INTEGER,
 receiver_name    VARCHAR(50) NOT NULL,
 receiver_address VARCHAR(128) NOT NULL,
 payment          REAL,
 status           BOOLEAN NOT NULL,
 deliver_date     DATE NOT NULL,
 request_date     DATE NOT NULL,
 fname            VARCHAR(50) NOT NULL,
 size             CHAR(1) NOT NULL,
 PRIMARY KEY(oid),
 FOREIGN KEY(cid) REFERENCES Customer(cid)
     ON DELETE CASCADE 
	 ON UPDATE CASCADE,
FOREIGN KEY(fname,size) REFERENCES Flowers(fname, size)
     ON DELETE CASCADE
	 ON UPDATE CASCADE
);


CREATE TABLE Contains(
 oid               INTEGER,
 number            INTEGER,
 fname VARCHAR(20) NOT NULL, 
 size CHAR(1) 
   CHECK(size IN('L', 'M', 'S')),  
FOREIGN KEY (fname, size) REFERENCES Flowers(fname, size)
   ON DELETE CASCADE,
FOREIGN KEY (oid) REFERENCES Order(oid)
   ON DELETE CASCADE
);


CREATE TABLE Flowers(
 fname     VARCHAR(50) 
 category  VARCHAR(15) NOT NULL,
 occasion  VARCHAR(30) NOT NULL,
 quantity  INTEGER NOT NULL,
 color     VARCHAR(10) NOT NULL,
 picture   VARCHAR(200) NOT NULL,
 size CHAR(1) 
   CHECK(size IN('L', 'M', 'S')),
 price REAL NOT NULL, 
PRIMARY KEY(fname, size)
);


CREATE TABLE Admin( 
 adname     VARCHAR(50)
 password CHAR(6) NOT NULL,
 PRIMARY KEY(name)
 );

 
